Imports System.Data
Imports System.Data.SqlClient

Partial Class QuickStats
  Inherits System.Web.UI.Page

  Protected Sub ddlDataSources_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlDataSources.DataBound
    ddlDataSources.Items.Insert(0, New ListItem("All Data Sources", 0))
  End Sub

  Protected Sub ddlVendors_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlVendors.DataBound
    ddlVendors.Items.Insert(0, New ListItem("All Vendors", 0))
  End Sub

  Protected Sub ddlVendors_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlVendors.SelectedIndexChanged
    If ddlDataSources.Items.Count = 0 Then
      ddlDataSources.DataBind()
    End If
  End Sub

  Protected Sub ddlStatsType_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlStatsType.DataBound
    ddlStatsType.Items.Insert(0, New ListItem("All Stats Types", 0))
    ddlStatsType.SelectedIndex = 2
  End Sub

  Protected Sub ddlColleges_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlColleges.DataBound
    ddlColleges.Items.Insert(0, New ListItem("All Colleges", 0))
  End Sub


  Protected Sub btnDisplay_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDisplay.Click
    ' build the sql statement based on all the pull downs and execute
    ' bind the results to the grid for display
    Dim sql As String, Months As String
    Dim EndDate As Date = ddlDate.SelectedValue
    '    Dim StartDate As New Date(EndDate.AddYears(-1).Year, EndDate.AddMonths(1).Month, 1)
    Dim StartDate As New Date(EndDate.AddMonths(-11).Year, EndDate.AddMonths(-11).Month, 1)

    ' build a pivot table from all the fun data
    sql = "SELECT *, isnull([1],0) + isnull([2],0) + isnull([3],0) + isnull([4],0) + isnull([5],0) + isnull([6],0) + isnull([7],0) + isnull([8],0) + isnull([9],0) + isnull([10],0) + isnull([11],0) + isnull([12],0) AS Total" _
      & " FROM (" _
      & " SELECT" _
      & " College, Vendor, Source, StatsType, month(month) AS monthnum, quantity" _
      & " FROM " _
      & " ReportStats " _
      & " WHERE " _
      & "   Month BETWEEN CONVERT(DATETIME, '" & StartDate.Year & "-" & StartDate.Month & "-" & StartDate.Day & "', 102)" _
      & "     AND CONVERT(DATETIME, '" & EndDate.Year & "-" & EndDate.Month & "-" & EndDate.Day & "', 102)"

    If ddlColleges.SelectedValue <> 0 Then
      sql = sql & " AND collegeid = " & ddlColleges.SelectedValue
    End If

    If ddlStatsType.SelectedValue <> 0 Then
      sql = sql & " AND statstypeid = " & ddlStatsType.SelectedValue
    End If

    If ddlDataSources.SelectedValue <> 0 Then
      sql = sql & " AND DataSourceVendorId = " & ddlDataSources.SelectedValue
    ElseIf ddlVendors.SelectedValue <> 0 Then
      sql = sql & " AND VendorId = " & ddlVendors.SelectedValue
    End If

    ' build correct month numbers for pivot table. may not always start with 1
    Months = " IN ("
    Dim i As Integer
    ' i is number of months to add from start date
    For i = 0 To 11
      Months = Months & "[" & StartDate.AddMonths(i).Month & "],"
    Next
    ' cleanup the dangling ,
    Months = Left(Months, Months.Length - 1) & ") ) "


    sql = sql & " ) AS o " _
      & " PIVOT ( " _
      & "   sum(Quantity) FOR monthnum" & Months _
      & " AS p ORDER BY College, Vendor, Source, StatsType"

    'Response.Write(sql & "<br>")

    ' open the connection, execute the sql and bind it to the datagrid
    Dim mySqlConnection As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("VendorStatsConnectionString").ToString)
    Dim mySqlCommand As SqlCommand = New SqlCommand(sql, mySqlConnection)
    Dim mySqlDatareader As SqlDataReader
    mySqlConnection.Open()

    mySqlCommand.CommandType = CommandType.Text
    mySqlDatareader = mySqlCommand.ExecuteReader()
    GridView1.DataSourceID = ""
    GridView1.DataSource = mySqlDatareader
    GridView1.AutoGenerateColumns = True
    GridView1.DataBind()

    mySqlDatareader.Close()
    mySqlConnection.Close()
  End Sub

  Protected Sub ddlDate_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlDate.Init
    ' put the last 24 months into the date pull down
    Dim myDate As New Date(Today().Year, Today().Month, 25)
    Dim i As Integer
    For i = 0 To 24
      myDate = myDate.AddMonths(-1)
      ddlDate.Items.Insert(i, New ListItem(myDate.Month & "/" & myDate.Year, myDate))
    Next
  End Sub

  Protected Sub GridView1_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.PreRender
    ' once the grid is built, add a header line every 20 lines to make it easier
    ' to read when scrolling. this is a nightmare. because of asp.net limitation we are looking
    ' at the grid but modifying the overlying table.

    Dim i, gridRowNdx, lookBackNdx, tblOffset As Integer

    Dim gvRow As GridViewRow
    Dim gvCells As TableCell()
    Dim cell As TableCell
    Dim tbl As Table

    For gridRowNdx = 20 To GridView1.Rows.Count-1 Step 20
      gvRow = New GridViewRow(-1, -1, DataControlRowType.Header, DataControlRowState.Normal)
      gvCells = New TableCell(16) {}

      ' add college to header line in grey if it is displayed
      ' have to look back to find the college
      For i = 0 To 1
        cell = New TableCell()
        If colVisible(i) Then
          For lookBackNdx = gridRowNdx To 0 Step -1
            If GridView1.Rows(lookBackNdx).Cells(i).Text <> "" Then
              cell.Text = GridView1.Rows(lookBackNdx).Cells(i).Text
              cell.Style("color") = "grey"
              cell.Style("text-align") = "left"
              Exit For
            End If
          Next
        End If
        gvCells(i) = cell
      Next

      ' blank columns 2 and 3
      For i = 2 To 3
        cell = New TableCell()
        gvCells(i) = cell
      Next

      ' total col
      cell = New TableCell()
      cell.Text = "Total"
      gvCells(16) = cell

      ' then plug in the dates
      Dim EndDate As Date = ddlDate.SelectedValue
      Dim StartDate As New Date(EndDate.AddMonths(-11).Year, EndDate.AddMonths(-11).Month, 1)
      For i = 4 To 15
        cell = New TableCell()
        cell.CssClass = "cb"
        cell.Text = StartDate.Month & "/" & Right(StartDate.Year, 2)
        gvCells(i) = cell
        StartDate = StartDate.AddMonths(1)
      Next
      gvRow.Cells.AddRange(gvCells)

      ' hide or display appropriate columns
      gvRow.Cells(0).Visible = ddlColleges.SelectedValue = 0
      gvRow.Cells(1).Visible = ddlVendors.SelectedValue = 0
      gvRow.Cells(2).Visible = ddlDataSources.SelectedValue = 0
      gvRow.Cells(3).Visible = ddlStatsType.SelectedValue = 0
      gvRow.Style("background-color") = "#E3EAEB"

      tbl = GridView1.Rows(gridRowNdx).Parent
      tblOffset += 1
      tbl.Rows.AddAt(gridRowNdx + tblOffset, gvRow)
    Next
  End Sub

  Dim textSave(2) As String, colVisible(4) As Boolean
  Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
    Dim i As Integer

    ' if a header line make the dates presentable
    If e.Row.RowType = DataControlRowType.Header Then
      Dim EndDate As Date = ddlDate.SelectedValue
      Dim StartDate As New Date(EndDate.AddMonths(-11).Year, EndDate.AddMonths(-11).Month, 1)
      For i = 4 To 15
        e.Row.Cells(i).Text = "&nbsp;" & StartDate.Month & "/" & Right(StartDate.Year, 2) & "&nbsp;"
        StartDate = StartDate.AddMonths(1)
      Next
      e.Row.Style("background-color") = "#E3EAEB"
      ' otherwise put commas in the numbers
    Else
      For i = 4 To 16
        Try ' some have &nbsp; rather than a number
          e.Row.Cells(i).Text = String.Format("{0:n0}", CType(e.Row.Cells(i).Text, Integer))
        Catch ex As Exception
        End Try
      Next
    End If

    ' remove colums that are not needed based on slection. for example, don;t show colleges
    ' if a specific college is selected
    ' tried many other places to do this other than at the cell level without success

    For i = 0 To 1
      If (colVisible(i)) Then
        ' blank out repeating values
        If e.Row.Cells(i).Text = textSave(i) Then
          e.Row.Cells(i).Text = ""
        Else
          textSave(i) = e.Row.Cells(i).Text
        End If
      End If
    Next

    'hide selectable values
    For i = 0 To 3
      e.Row.Cells(i).Visible = colVisible(i)
      e.Row.Cells(i).Style("text-align") = "left"
    Next
  End Sub
  Protected Sub GridView1_DataBinding(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.DataBinding
    'hide other selectable values
    colVisible(0) = (ddlColleges.SelectedValue = 0)
    colVisible(1) = (ddlVendors.SelectedValue = 0)
    colVisible(2) = (ddlDataSources.SelectedValue = 0)
    colVisible(3) = (ddlStatsType.SelectedValue = 0)
  End Sub
End Class
